package com.xjy.ftpmanagment.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;

import com.xjy.common.dao.BaseDao;
import com.xjy.common.util.PageBean;
import com.xjy.ftpmanagment.pojo.StatFile;

public class StatFileDao extends BaseDao<StatFile, Integer>{
	
	/**
	 * 批量插入List<StatFile>
	 * @param statFilelist
	 * @return
	 */
	public int[] batchInsert(List<StatFile> statFilelist, Timestamp startTime, Timestamp endTime) {
		logger.info("Delete from stat_file from " + startTime + " to " + endTime);
		jdbcTemplate.execute("DELETE FROM stat_file WHERE STAT_TIME>'" + startTime + "' and STAT_TIME<'" + endTime +"'");
		logger.debug("batchInsert StatFile");
		final List<StatFile> list = statFilelist;
		String sql = "INSERT INTO stat_file(FILE_COUNT, FILE_NAME, STAT_TIME, CREATE_TIME)" +
				"VALUES (?,?,?,?)";
		BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setInt(1, list.get(i).getFileCount());
				ps.setString(2, list.get(i).getFileName());
				ps.setTimestamp(3, list.get(i).getStatTime());
				ps.setTimestamp(4, list.get(i).getCreateTime());
			}
			@Override
			public int getBatchSize() {
				return list.size();
			}
		};
		return jdbcTemplate.batchUpdate(sql, setter);
	}

	public PageBean queryForPage(int page, int pageSize, String condition) {
		PageBean pageBean = getPageBean(condition, null, "", page, pageSize);
//		List list = jdbcTemplate.queryForList("SELECT _user,count(ID) record_num,sum(conn) conn,sum(down_file) down_file,sum(down_mb) down_mb,sum(up_file) up_file,sum(up_mb) up_mb FROM stat_user GROUP BY _user ORDER BY down_mb DESC");
		StringBuilder hql = new StringBuilder();
		hql.append("SELECT obj.fileName,sum(obj.fileCount) AS fileCount");
		hql.append(" " + FROM + getEntityClass().getName() + " obj");
		if (condition != null && !condition.trim().equals("")) {
			hql.append(WHERE + condition);
		}
		hql.append(" GROUP BY obj.fileName ORDER BY sum(obj.fileCount) DESC");
		List list = findByHql(hql.toString());
		List<StatFile> statFileList = new ArrayList<StatFile>();
		for(Object o : list) {
			Object[] obj = (Object[])o;
			StatFile sf = new StatFile();
			sf.setFileName(obj[0].toString());
			sf.setFileCount(Integer.parseInt(obj[1].toString()));
			statFileList.add(sf);
		}
//		pageBean.setPageSize(pageSize);
//		pageBean.setCurrentPage(currentPage);
//		pageBean.setTotalPage(totalPage);
//		pageBean.setList(list);
		return PageBean.packagePageBean(pageSize,page,statFileList,statFileList.size());
	}
}
